package com.huabo.contract.oracle.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblContractProject;
import com.huabo.contract.oracle.vo.TblContractProjectVo;

public class TblContractProjectMapperSqlConfig {
	public String selectPlanCodeByOrgid(TblContractProject plan) {
		StringBuffer sb = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_PROJECT WHERE 1=1 AND PROJECTCODE='"+plan.getProjectcode()+"' ");
		if(plan.getProjectid() != null) {
			sb.append(" AND PROJECTID != "+plan.getProjectid());
		}
		return sb.toString();
	}
	
	public String selectListByPageInfo(PageInfo<TblContractProject> pageInfo,TblContractProjectVo tblContractProjectVo,Integer staffid) {
		StringBuffer sb = new StringBuffer("SELECT * FROM "
				+ "(SELECT T1.*,ROWNUM RN  FROM "
				+ "(SELECT TNA.*,STAFF.REALNAME,ORG.ORGNAME "
				+ "FROM TBL_CONTRACT_PROJECT TNA "
				+ "LEFT JOIN TBL_STAFF STAFF ON STAFF.STAFFID = TNA.UNDERTAKESTAFFID "
				+ "LEFT JOIN TBL_ORGANIZATION ORG ON ORG.ORGID = TNA.UNDERTAKEORGID "
				+ "WHERE 1=1 "
				+ " AND TNA.CREATESTAFFID='"+staffid+"' ");
		
		if(tblContractProjectVo.getProjectcode()!=null && tblContractProjectVo.getProjectcode().length()>0) {
			sb.append(" AND TNA.PROJECTCODE LIKE '%"+tblContractProjectVo.getProjectcode()+"%'");
		}
		
		if(tblContractProjectVo.getProjectname()!=null && tblContractProjectVo.getProjectname().length()>0){
			sb.append(" AND TNA.PROJECTNAME LIKE '%"+tblContractProjectVo.getProjectname()+"%'");
		}
		
		sb.append(" ORDER BY TNA.PROJECTID DESC) T1 WHERE ROWNUM <= "+(pageInfo.getCurrentRecord()+pageInfo.getPageSize())+" ) T2 WHERE T2.RN > "+pageInfo.getCurrentRecord());
		return sb.toString();
	}
	
	public String selectCountByPageInfo(PageInfo<TblContractProject> pageInfo,TblContractProjectVo tblContractProjectVo,Integer staffid) {
		StringBuffer sb = new StringBuffer("SELECT COUNT(0) "
				+ "FROM TBL_CONTRACT_PROJECT TNA "
				+ "WHERE 1=1 "
				+ " AND TNA.CREATESTAFFID='"+staffid+"' ");
		
		if(tblContractProjectVo.getProjectcode()!=null && tblContractProjectVo.getProjectcode().length()>0) {
			sb.append(" AND TNA.PROJECTCODE LIKE '%"+tblContractProjectVo.getProjectcode()+"%'");
		}
		
		if(tblContractProjectVo.getProjectname()!=null && tblContractProjectVo.getProjectname().length()>0){
			sb.append(" AND TNA.PROJECTNAME LIKE '%"+tblContractProjectVo.getProjectname()+"%'");
		}
		
		return sb.toString();
	}
	
	public String updateEntity(TblContractProject plan) {
		StringBuffer sqlSb = new StringBuffer("UPDATE TBL_CONTRACT_PROJECT SET PROJECTNAME = '"+plan.getProjectname()+"' ");
		if(plan.getProjectcode() != null && !"".equals(plan.getProjectcode())) {
			sqlSb.append(" ,PROJECTCODE = '"+plan.getProjectcode()+"'");
		}
		if(plan.getMemo() != null && !"".equals(plan.getMemo())) {
			sqlSb.append(" ,MEMO = '"+plan.getMemo()+"'");
		}
		if(plan.getUndertakeorgid() != null && !"".equals(plan.getUndertakeorgid())) {
			sqlSb.append(" ,UNDERTAKEORGID = '"+plan.getUndertakeorgid()+"'");
		}
		if(plan.getUndertakestaffid() != null && !"".equals(plan.getUndertakestaffid())) {
			sqlSb.append(" ,UNDERTAKESTAFFID = '"+plan.getUndertakestaffid()+"'");
		}
		sqlSb.append(" WHERE PROJECTID= "+plan.getProjectid());
		return sqlSb.toString();
	}
	
	public String insertEntity(TblContractProject plan){
		StringBuffer colSb = new StringBuffer("INSERT INTO TBL_CONTRACT_PROJECT(PROJECTID,CREATETIME,STATE,CREATESTAFFID");
		StringBuffer valSb = new StringBuffer("  VALUES (HIBERNATE_SEQUENCE.nextval,TO_DATE('"+DateUtil.parseDate(plan.getCreatetime(), "yyyy-MM-dd HH:mm:ss")+"','YYYY-MM-DD HH24:mi:ss'),0,"+plan.getCreatestaffid());
		
		if(plan.getProjectcode() != null && !"".equals(plan.getProjectcode())) {
			colSb.append(",PROJECTCODE");
			valSb.append(",'"+plan.getProjectcode()+"'");
		}
		
		if(plan.getProjectname() != null && !"".equals(plan.getProjectname())) {
			colSb.append(",PROJECTNAME");
			valSb.append(",'"+plan.getProjectname()+"'");
		}
		
		if(plan.getMemo() != null && !"".equals(plan.getMemo())) {
			colSb.append(",MEMO");
			valSb.append(",'"+plan.getMemo()+"'");
		}
		
		if(plan.getUndertakeorgid() != null && !"".equals(plan.getUndertakeorgid())) {
			colSb.append(",UNDERTAKEORGID");
			valSb.append(","+plan.getUndertakeorgid()+"");
		}
		
		if(plan.getUndertakestaffid() != null && !"".equals(plan.getUndertakestaffid())) {
			colSb.append(",UNDERTAKESTAFFID");
			valSb.append(","+plan.getUndertakestaffid()+"");
		}
		
		String sql = colSb.toString()+")"+valSb.toString()+")";
		return sql;
	}
}
